<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()) {
String newAccountName = null;
String oldISBN = null;
String newISBN = null;
String newEdition = null;
String newCopyCondition = null;
String newCopyPrice = null;

newAccountName = request.getParameter("username");
oldISBN = request.getParameter("oldisbn");
newISBN = request.getParameter("newisbn");
newEdition = request.getParameter("edition");
newCopyCondition = request.getParameter("copycondition");
newCopyPrice = request.getParameter("copyprice");

int count_user = 0;
Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT COUNT(*) AS Count FROM Users WHERE AccountName = '" + newAccountName + "';";
rs0 = statement0.executeQuery(query0);
if(rs0.next()) {
count_user = Integer.parseInt(rs0.getString("Count"));

}
statement0.close();

if(count_user == 0) {
out.print("user_not_logged_in");

}
else {
Statement statement1 = null;
ResultSet rs1 = null;
statement1 = connection.createStatement();
String query1 = "SELECT * FROM Books WHERE ISBN = '" + oldISBN + "';";
rs1 = statement1.executeQuery(query1);

if(rs1.next()) {
//check whether newISBN already exists
int count_existing = 0;
Statement statementE = null;
ResultSet rsE = null;
statementE = connection.createStatement();
String queryE = "SELECT COUNT(*) AS Count FROM Books WHERE ISBN = '" + newISBN + "';";
rsE = statementE.executeQuery(queryE);

if(rsE.next()){
count_existing = Integer.parseInt(rsE.getString("Count"));

}
statementE.close();

if(count_existing != 0) {
out.print("new_isbn_already_exist");

}
else {
String oldBookTitle = null;
String oldCourses = null;
String oldAuthors = null;
String oldPublisher = null;

oldBookTitle = rs1.getString("Books.BookTitle");
oldCourses = rs1.getString("Books.Courses");
oldAuthors = rs1.getString("Books.Authors");
oldPublisher = rs1.getString("Books.Publisher");

int count_newbook = 0;
Statement statement2 = null;
statement2 = connection.createStatement();
String query2 = "INSERT INTO Books VALUES ('" + oldBookTitle + "', " + newEdition + ", '" + oldCourses + "', '" + oldAuthors + "', '" + oldPublisher + "', '" + newISBN + "');";
count_newbook = statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
statement2.close();

int newCopyID = 0;
Statement statement3 = null;
statement3 = connection.createStatement();
String query3 = "INSERT INTO BookCopies VALUES (DEFAULT, '" + newISBN + "', '" + newCopyCondition + "', " + newCopyPrice + ", DEFAULT, '" + newAccountName + "', DEFAULT);";
statement3.executeUpdate(query3, Statement.RETURN_GENERATED_KEYS);
ResultSet rs3 = statement3.getGeneratedKeys();
if(rs3.next()) {
newCopyID = rs3.getInt(1);

}
statement3.close();

int count_record = 0;
Statement statement4 = null;
statement4 = connection.createStatement();
String query4 = "INSERT INTO Sell Values (DEFAULT, '" + newAccountName + "', " + newCopyID + ", DEFAULT, DEFAULT);";
count_record = statement4.executeUpdate(query4, Statement.NO_GENERATED_KEYS);
statement4.close();

if(count_record != 0) {
out.print("insert_successful");

}

}

}
statement1.close();

}

}
connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>